package com.wxtf.platform.main.report;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.DecimalFormat;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.util.CellRangeAddress;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.Common;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.FormatUtil;
import org.apache.commons.lang.StringUtils;
/**
 * <li>Title: TmaMedicineVistorReport.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.report</li>
 * <li>Description: 药具使用随访汇总</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Aug 21, 2012 10:29:02 AM</li>
 * 
 * @author liu_zhaojun
 * @version 1.0.0.0
 * 
 */
public class TmaMedicineVistorReport extends LogicBase implements CommonsCallProvider{
	private static final Log log = LogFactory.getLog(TmaMedicineVistorReport.class);

	public TmaMedicineVistorReport(String jsonArray){

	}
	
	@Override
	public Object run() {
		Object msg = "";
		try {
			if("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
			}else if("save".equals(this.getMethodName())) {
				msg = this.insert(this.getLogicData());
			}else if("selectPerson".equals(this.getMethodName())) {
				msg = this.selectPerson(this.getLogicData());
			}else if("expData".equals(this.getMethodName())) {
				msg = this.expData(this.getLogicData());
			}else if("isReport".equals(this.getMethodName())) {
				msg = this.isReport(this.getLogicData());
			}else if("selectYear".equals(this.getMethodName())) {
				msg = this.selectYear(this.getLogicData());
			}else if("selectQuarter".equals(this.getMethodName())) {
				msg = this.selectQuarter(this.getLogicData());
			}else if("selectHistory".equals(this.getMethodName())) {
				msg = this.selectHistory(this.getLogicData());
			}else if("selectCreateByAndCreateTime".equals(this.getMethodName())) {
				msg = this.selectCreateByAndCreateTime(this.getLogicData());
			} else {
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}
			if(msg == null || "[null]".equals(msg)) {
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(e, e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}
	/** 
	 * 描述：selectYear 查询年
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Jul 10, 2012  5:12:42 PM
	 * @author liu_zhaojun
	 */
	public Object selectYear(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		String org_id = user.getCurOrgId();
		StringBuffer buff = new StringBuffer("select distinct(SUBSTR(R_QUARTER,1,4)) from tma_medicine_vistor_report");
		buff.append(" where org_id='").append(org_id).append("'");
		List<Object> list = AccessDataBaseUtil.query4List(buff.toString(), null);
		List<Map<String,String>> temp = new ArrayList<Map<String,String>>();
		if(list!=null){
			for(int i=0;i<list.size();i++){
				Map<String,String> map = new HashMap<String,String>();
				map.put("nian", list.get(i).toString());
				temp.add(map);
			}
		}
		JSONArray array = JSONArray.fromObject(temp);
		return array;
	}
	/** 
	 * 描述：selectQuarter 查询季度
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Jul 10, 2012  5:12:42 PM
	 * @author liu_zhaojun
	 */
	public Object selectQuarter(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		String org_id = user.getCurOrgId();
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String jidu = jsonObject.getString("jidu");
		StringBuffer buff = new StringBuffer("select distinct(SUBSTR(R_QUARTER,6,1)) from tma_medicine_vistor_report");
		buff.append(" where R_QUARTER like '%").append(jidu).append("%' and org_id='").append(org_id).append("' order by R_QUARTER");
		List<Object> list = AccessDataBaseUtil.query4List(buff.toString(), null);
		List<Map<String,String>> temp = new ArrayList<Map<String,String>>();
		if(list!=null){
			for(int i=0;i<list.size();i++){
				Map<String,String> map = new HashMap<String,String>();
				map.put("nian", list.get(i).toString());
				temp.add(map);
			}
		}
		array = JSONArray.fromObject(temp);
		return array;
	}
	/** 
	 * 描述：selectCreateByAndCreateTime 查询人和时间
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Jul 10, 2012  5:12:42 PM
	 * @author liu_zhaojun
	 */
	public Object selectCreateByAndCreateTime(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		String org_id = user.getCurOrgId();
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String jidu = jsonObject.getString("jidu");
		StringBuffer buff = new StringBuffer("select CREATE_BY,CREATE_TIME from tma_medicine_vistor_report");
		buff.append(" where R_QUARTER like '%").append(jidu).append("%' and org_id='").append(org_id).append("' GROUP BY ORG_ID");
		String[] jsonField = {"CREATE_BY","CREATE_TIME"};
		List<Map<String,String>> list = AccessDataBaseUtil.query(jsonField, buff.toString(), null);
		array = JSONArray.fromObject(list);
		return array;
	}
	/** 
	 * 描述：selectHistory 查看历史
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Jul 10, 2012  5:12:42 PM
	 * @author liu_zhaojun
	 */
	public Object selectHistory(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		String org_id = user.getCurOrgId();
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String jidu = jsonObject.getString("jidu");
		DecimalFormat format = new DecimalFormat("0.00");
		StringBuffer buff = new StringBuffer("select tmad_id,r_name,r_company_name,r_batch_code,r_renshu,r_code,r_lishu,r_fashenglv,r_yuefen,r_you,r_wu,r_beizhu from tma_medicine_vistor_report");
		buff.append(" where R_QUARTER like '%").append(jidu).append("%' and org_id='").append(org_id).append("' order by tmad_id,r_code");
		String[] fields = {"tmad_id","tmad_name","tmad_company_name","tmad_batch_code","renshu","tmgd_code","lishu","fashenglv","yuefen","you","wu","beizhu"};
		List<Map<String, String>> temp = AccessDataBaseUtil.query(fields, buff.toString(), null);
		for(int i=0;i<temp.size();i++){
			Map<String, String> map = temp.get(i);
			map.put("fashenglv", format.format(Double.valueOf(map.get("fashenglv"))));
			map.put("renshu", Double.valueOf(map.get("renshu"))==0?null:Double.valueOf(map.get("renshu")).intValue()+"");
			map.put("lishu", Double.valueOf(map.get("lishu"))==0?null:Double.valueOf(map.get("lishu")).intValue()+"");
			map.put("yuefen", Double.valueOf(map.get("yuefen"))==0?null:Double.valueOf(map.get("yuefen")).intValue()+"");
			map.put("you", "null".equals(map.get("you"))?null:Double.valueOf(map.get("you")).intValue()+"");
			map.put("wu", "null".equals(map.get("wu"))?null:Double.valueOf(map.get("wu")).intValue()+"");
			temp.set(i, map);
		}
		String json = JSONArray.fromObject(temp).toString();
		return "{\"Rows\":" + json + "}";
	}
	
	/** 
	 * 描述：isReport 是否上报
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Jul 10, 2012  5:12:42 PM
	 * @author liu_zhaojun
	 */
	public Object isReport(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		String org_id = user.getCurOrgId();
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String r_quarter = jsonObject.getString("r_quarter");
		StringBuffer buff = new StringBuffer("select count(r_is_valid),sum(r_is_valid) from tma_medicine_vistor_report");
		buff.append(" where r_quarter='").append(r_quarter).append("' and org_id='").append(org_id).append("'");
		List<Object[]> list = AccessDataBaseUtil.query(buff.toString(), null);
		int count = 0;
		int sum = 0;
		if(list!=null&&list.size()>0){
			count = Integer.valueOf(list.get(0)[0].toString());
			if(count > 0){
				sum = Double.valueOf(list.get(0)[1].toString()).intValue();
			}
		}
		return count==0?count:sum;
	}
	
	/** 
	 * 描述：selectPerson 查询服药人数
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Jul 10, 2012  5:12:42 PM
	 * @author liu_zhaojun
	 */
	public Object selectPerson(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tmad_id = jsonObject.getString("tmad_id");
		String tmgd_code = jsonObject.getString("tmgd_code");		
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		String org_id = user.getCurOrgId();
		String now = FormatUtil.getCurentDateTime();
		String before = getTime("'"+org_id+"'");
		StringBuffer str = new StringBuffer("");
		sql = "select department_id from t_department where dparent_id=?";
		List<Object> subList = AccessDataBaseUtil.query4List(sql, new Object[]{org_id});
		if(subList!=null&&subList.size()>0){
			for(int i=0;i<subList.size();i++){
				str.append("'").append(subList.get(i)).append("'");
				if(i<subList.size()-1){
					str.append(",");
				}
			}
			str = fn(str);	
		}else{
			str.append("'").append(org_id).append("'");
		}
		StringBuffer buff = new StringBuffer(" from tma_dictionary a");
		buff.append(" inner join (select c.tbe_id,c.tmamvm_cmedicine_des,c.tmamvm_lmedicine_time,e.tmgd_code,d.tmamvd_same_pharmacy_des,emp.tbe_name,emp.tbe_idcard,emp.tbe_sex from tma_medicine_vistor_main c inner join tb_employee emp on c.tbe_id=emp.tbe_id inner join tma_medicine_vistor_detail d on c.tmamvm_id=d.tmamvm_id and c.tmamvm_date between '").append(before).append("' and '").append(now).append("' and c.org_id in (").append(str).append(") inner join tm_general_dictionary e on d.tmamvd_side_effect_code=e.tmgd_id");
		if(StringUtils.isNotBlank(tmgd_code)){
			buff.append(" and e.tmgd_code not in('00')");
		}		
		buff.append(" group by c.tbe_id");
		if(StringUtils.isNotBlank(tmgd_code)){
			buff.append(",e.tmgd_code");
		}
		buff.append(" order by tmamvm_date desc) cde on a.tmad_id=cde.tmamvm_cmedicine_des where a.tmad_id='").append(tmad_id).append("'");
		if(StringUtils.isNotBlank(tmgd_code)){
			buff.append(" and cde.tmgd_code='").append(tmgd_code).append("'");
		}		
		buff.append(" order by a.tmad_name,cde.tmgd_code");
		String countSql = "select count(*) " + buff.toString();
		String querySql = "select a.tmad_name,a.tmad_company_name,a.tmad_batch_code,cde.tbe_id,cde.tbe_name,cde.tbe_idcard,cde.tbe_sex,cde.tmgd_code,cde.tmamvm_lmedicine_time,cde.tmamvd_same_pharmacy_des" + buff.toString();
		String[] fields = {"tmad_name","tmad_company_name","tmad_batch_code","tbe_id","tbe_name","tbe_idcard","tbe_sex","tmgd_code","tmamvm_lmedicine_time","tmamvd_same_pharmacy_des"};
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}
	public StringBuffer fn(StringBuffer temp) throws Exception {
		StringBuffer str = new StringBuffer("select department_id from t_department where dparent_id in (");
		str.append(temp.toString()).append(")");
		List<Object> subList  = AccessDataBaseUtil.query4List(str.toString(), null);
		if(subList!=null&&subList.size()>0){
			temp.setLength(0);
			for(int i=0;i<subList.size();i++){
				temp.append("'").append(subList.get(i)).append("'");
				if(i<subList.size()-1){
					temp.append(",");
				}
			}			
		}
		str = new StringBuffer("select org_id from tma_medicine_vistor_report where r_is_valid='1' and org_id in (").append(temp.toString()).append(") group by org_id order by create_time desc");
		subList  = AccessDataBaseUtil.query4List(sql, null);		
		if(subList!=null&&subList.size()>0){
			temp.setLength(0);
			for(int j=0;j<subList.size();j++){
				temp.append("'").append(subList.get(j)).append("'");
				if(j<subList.size()-1){
					temp.append(",");
				}
			}
			fn(temp);
		}		
		return temp;
	}
	
	/**
	 * 描述：getTime 上次上报时间
	 * @param org_id
	 * @return
	 * @throws Exception
	 */
	public String getTime(String org_id) throws Exception {
		StringBuffer buff = new StringBuffer("select a.create_time from (select org_id,create_time from tma_medicine_vistor_report");
		buff.append(" where r_is_valid='1' and org_id in (").append(org_id).append(") order by create_time desc) a group by a.org_id");
		List<Object> list = AccessDataBaseUtil.query4List(buff.toString(), null);
		return list!=null&&list.size()>0?list.get(0).toString():"";
	}
	
	/** 
	 * 描述：select 随访汇总
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Jul 10, 2012  5:12:42 PM
	 * @author liu_zhaojun
	 */
	@Override
	public Object select(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		String org_id = user.getCurOrgId();
		List<Map<String, String>> temp = null;
		String now = FormatUtil.getCurentDateTime();
		String before = getTime("'"+org_id+"'");
		DecimalFormat format = new DecimalFormat("0.00");
		sql = "select department_id from t_department where dparent_id=?";
		List<Object> subList = AccessDataBaseUtil.query4List(sql, new Object[]{org_id});
		if(subList!=null&&subList.size()>0){
			StringBuffer str = new StringBuffer("");
			for(int i=0;i<subList.size();i++){
				str.append("'").append(subList.get(i)).append("'");
				if(i<subList.size()-1){
					str.append(",");
				}
			}
			StringBuffer buff = new StringBuffer("select c.tmad_id,c.r_name,c.r_company_name,c.r_batch_code,c.renshu,b.r_code,b.lishu,b.lishu/c.renshu*100 as fashenglv,b.yuefen,b.you,b.wu from");
			buff.append(" (select sum(a.r_lishu) as lishu,sum(a.r_yuefen) as yuefen,sum(a.r_you) as you,sum(a.r_wu) as wu,a.tmad_id,a.r_code from tma_medicine_vistor_report a where a.r_is_valid='1' and a.create_time between '").append(before).append("' and '").append(now).append("' and a.org_id in (").append(str).append(") group by a.tmad_id,a.r_code) b left join") 
			.append(" (select sum(d.renshu) as renshu,d.tmad_id,d.r_name,d.r_company_name,d.r_batch_code from (select sum(distinct a.r_renshu) as renshu,a.tmad_id,a.r_name,a.r_company_name,a.r_batch_code from tma_medicine_vistor_report a where a.r_is_valid='1' and a.create_time between '").append(before).append("' and '").append(now).append("' and a.org_id in (").append(str).append(") group by a.tmad_id,a.org_id) d group by d.tmad_id) c on b.tmad_id=c.tmad_id");
			String[] fields = {"tmad_id","tmad_name","tmad_company_name","tmad_batch_code","renshu","tmgd_code","lishu","fashenglv","yuefen","you","wu"};
			temp = AccessDataBaseUtil.query(fields, buff.toString(), null);
			for(int i=0;i<temp.size();i++){
				Map<String, String> map = temp.get(i);
				map.put("fashenglv", format.format(Double.valueOf(map.get("fashenglv"))));
				map.put("renshu", Double.valueOf(map.get("renshu"))==0?null:Double.valueOf(map.get("renshu")).intValue()+"");
				map.put("lishu", Double.valueOf(map.get("lishu"))==0?null:Double.valueOf(map.get("lishu")).intValue()+"");
				map.put("yuefen", Double.valueOf(map.get("yuefen"))==0?null:Double.valueOf(map.get("yuefen")).intValue()+"");
				map.put("you", Double.valueOf(map.get("you"))==0?null:Double.valueOf(map.get("you")).intValue()+"");
				map.put("wu", Double.valueOf(map.get("wu"))==0?null:Double.valueOf(map.get("wu")).intValue()+"");
				map.put("beizhu", null);
				temp.set(i, map);
			}
		}else{			
			StringBuffer buff = new StringBuffer("select f.tmad_id,f.tmad_name,f.tmad_company_name,f.tmad_batch_code,d.tbe_id,d.tmamvd_same_pharmacy_des,d.tmgd_code,d.yuefen,e.renshu"); 
			buff.append(" from tma_dictionary f inner join")
			.append(" (select d.TMAMVM_CMEDICINE_DES,count(distinct d.TBE_ID) as renshu from (select DISTINCT(c.TMAMVM_CMEDICINE_DES) as TMAMVM_CMEDICINE_DES,c.TBE_ID from (select a.TBE_ID,a.TMAMVM_CMEDICINE_DES from tma_medicine_vistor_main a,tma_medicine_vistor_detail b where a.TMAMVM_ID=b.TMAMVM_ID and b.tmamvd_time between '").append(before).append("' and '").append(now).append("' and a.org_id='").append(org_id).append("') c) d group by d.TMAMVM_CMEDICINE_DES) e")
			.append(" on f.tmad_id=e.tmamvm_cmedicine_des")
			.append(" inner join (select a.tbe_id,a.tmamvm_cmedicine_des,b.tmamvd_same_pharmacy_des,c.tmgd_code,count(c.tmgd_code) as yuefen from tma_medicine_vistor_main a inner join tma_medicine_vistor_detail b on a.tmamvm_id=b.tmamvm_id and b.tmamvd_time between '").append(before).append("' and '").append(now).append("' and a.org_id='").append(org_id).append("' inner join tm_general_dictionary c on b.tmamvd_side_effect_code=c.tmgd_id and c.tmgd_code not in('00') group by a.tbe_id,a.tmamvm_cmedicine_des,b.tmamvd_same_pharmacy_des,c.tmgd_code order by a.tmamvm_date desc) d") 
			.append(" on f.tmad_id=d.tmamvm_cmedicine_des")
			.append(" order by f.tmad_id,d.tmgd_code");
			String[] fields = {"tmad_id","tmad_name","tmad_company_name","tmad_batch_code","tbe_id","tmamvd_same_pharmacy_des","tmgd_code","yuefen","renshu"};
			List<Map<String, String>> list = AccessDataBaseUtil.query(fields, buff.toString(), null);
			temp = new ArrayList<Map<String, String>>();
			if(list != null && list.size() > 0){
				String tmad_name = "";
				String tmad_company_name = "";
				String tmad_batch_code = "";
				String tmgd_code = "";
				int lishu = 1;
				boolean bool = false;
				Map<String, String> m = null;
				for(int i=0;i<list.size();i++){
					int num = 0;
					Map<String, String> map = list.get(i);
					String tmamvd_same_pharmacy_des = map.get("tmamvd_same_pharmacy_des");
					if("1".equals(tmamvd_same_pharmacy_des)){
						map.put("you", map.get("yuefen"));
					}
					if("0".equals(tmamvd_same_pharmacy_des)){
						map.put("wu", map.get("yuefen"));
					}
					map.put("renshu", map.get("renshu"));
					map.put("beizhu", null);
					if(tmad_name.equals(map.get("tmad_name"))&&tmad_company_name.equals(map.get("tmad_company_name"))&&tmad_batch_code.equals(map.get("tmad_batch_code"))&&tmgd_code.equals(map.get("tmgd_code"))){
						m = list.get(i - 1);
						bool = false;
						map.put("tmad_name", tmad_name);
						map.put("tmad_company_name", tmad_company_name);
						map.put("tmad_batch_code", tmad_batch_code);
						map.put("tmgd_code", tmgd_code);
						if(!m.get("tbe_id").equals(map.get("tbe_id"))){
							lishu += 1;
						}						
						map.put("lishu", lishu+"");
						Integer yuefen = Integer.valueOf(m.get("yuefen"))+Integer.valueOf(map.get("yuefen"));
						map.put("yuefen", yuefen+"");
						fn(map,m,"you");
						fn(map,m,"wu");
						m = map;
					}else{
						tmad_name = map.get("tmad_name");
						tmad_company_name = map.get("tmad_company_name");
						tmad_batch_code = map.get("tmad_batch_code");
						tmgd_code = map.get("tmgd_code");
						if(m == null){
							m = map;
						}else{						
							bool = true;
							num = num + 1;
							lishu = 1;
							m = list.get(i - 1);
						}
						fn(m,"you");
						fn(m,"wu");
					}
					if(i == list.size() - 1){
						bool = true;
						num = num + 1;
					}
					if(bool){
						if(!m.containsKey("lishu")){
							m.put("lishu", lishu+"");
						}
						Double fashenglv = Double.valueOf(m.get("lishu"))/Double.valueOf(m.get("renshu"))*100;
						m.put("fashenglv", format.format(fashenglv));
						temp.add(m);
					}
					if(num==2){
						if(!map.containsKey("lishu")){
							map.put("lishu", lishu+"");
						}
						Double fashenglv = Double.valueOf(map.get("lishu"))/Double.valueOf(map.get("renshu"))*100;
						map.put("fashenglv", format.format(fashenglv));
						fn(map,"you");
						fn(map,"wu");
						temp.add(map);
					}
				}
			}
		}
		String json = JSONArray.fromObject(temp).toString();
		return "{\"Rows\":" + json + "}";
	}
	public void fn(Map<String, String> m,String str){
		if(m.get(str)==null){
			m.put(str, m.get(str));
		}else{
			m.put(str, m.get(str));
		}
	}
	public void fn(Map<String, String> map,Map<String, String> m,String str){
		if(m.get(str)!=null&&map.get(str)!=null){
			Integer temp = Integer.valueOf(m.get(str))+Integer.valueOf(map.get(str));
			map.put(str, temp.toString());
		}else if(m.get(str)==null){
			map.put(str, map.get(str));
		}else if(map.get(str)==null){
			map.put(str, m.get(str));
		}
	}
	/** 
	 * 描述：insert 随访汇总增加
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Jul 10, 2012  5:12:42 PM
	 * @author liu_zhaojun
	 */
	@Override
	public Object insert(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		String org_id = user.getCurOrgId();
		String now = FormatUtil.getCurentDateTime();
		String before = getTime("'"+org_id+"'");
		StringBuffer str = new StringBuffer("select a.department_id,a.caption from ");
		str.append("(select department_id,caption from t_department where dparent_id='")
		.append(org_id)
		.append("') a where a.department_id not in (select org_id from tma_medicine_vistor_report where create_time between '").append(before).append("' and '").append(now).append("' and org_id in((select department_id from t_department where dparent_id='")
		.append(org_id)
		.append("')))");
		List<Object[]> subList = AccessDataBaseUtil.query(str.toString(), null);
		if(subList!=null&&subList.size()>0){
			str.setLength(0);
			str.append("[");
			for(int i=0;i<subList.size();i++){
				str.append("").append(subList.get(i)[1]).append("");
				if(i < subList.size() - 1){
					str.append("，");
				}
			}
			str.append("]");
			if(str!=null&&!"".equals(str)){
				return "\"机构"+str.toString()+"没有上报\"";
			}
		}
		str.setLength(0);
		str.append("delete from tma_medicine_vistor_report where r_is_valid='0' and org_id='").append(org_id).append("'");
		AccessDataBaseUtil.executeSQLUpdate(str.toString());
		List<String> list = new ArrayList<String>();
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tianbaoren = jsonObject.getString("tianbaoren");
		String tianbaoriqi = FormatUtil.getCurentDateTime();
		String r_is_valid = jsonObject.getString("r_is_valid");
		String r_quarter = jsonObject.getString("r_quarter");
		String ids = jsonObject.getString("ids");
		array = JSONArray.fromObject(ids);
		for(int i=0;i<array.size();i++){
			jsonObject = array.getJSONObject(i);
			StringBuffer buff = new StringBuffer("insert into tma_medicine_vistor_report(r_id,org_id,tmad_id,r_name,r_company_name,r_batch_code,r_renshu,r_code,r_lishu,r_fashenglv,r_is_valid,r_quarter,r_yuefen,r_you,r_wu,r_beizhu,CREATE_BY,CREATE_TIME) values(");
			buff.append("'").append(AccessDataBaseUtil.getPKUUID()).append("'")
			.append(",'").append(org_id).append("'")
			.append(",'").append(jsonObject.getString("tmad_id")).append("'")
			.append(",'").append(jsonObject.getString("tmad_name")).append("'")
			.append(",'").append(jsonObject.getString("tmad_company_name")).append("'")
			.append(",'").append(jsonObject.getString("tmad_batch_code")).append("'")
			.append(",'").append(jsonObject.getString("renshu")).append("'")
			.append(",'").append(jsonObject.getString("tmgd_code")).append("'")
			.append(",'").append(jsonObject.getString("lishu")).append("'")
			.append(",'").append(jsonObject.getString("fashenglv")).append("'")
			.append(",'").append(r_is_valid).append("'")
			.append(",'").append(r_quarter).append("'")
			.append(",'").append(jsonObject.getString("yuefen")).append("'")
			.append(",'").append(jsonObject.getString("you")).append("'")
			.append(",'").append(jsonObject.getString("wu")).append("'")
			.append(",'").append(jsonObject.getString("beizhu")).append("'")			
			.append(",'").append(tianbaoren).append("'")
			.append(",'").append(tianbaoriqi).append("')");
			list.add(buff.toString());
		}		
		AccessDataBaseUtil.executeSQLUpdate(list);
		return "\"保存成功\"";
	}
	
	/** 
	 * 描述：expData 导出
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Sep 5, 2012  5:06:00 PM
	 * @author liu_zhaojun
	 */
	public Object expData(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String ids = jsonObject.getString("ids");
		String tianbaoren = jsonObject.getString("tianbaoren");
		String tianbaojigou = jsonObject.getString("tianbaojigou");
		String tianbaoriqi = jsonObject.getString("tianbaoriqi");
		List<Object[]> list = new ArrayList<Object[]>();
		array = JSONArray.fromObject(ids);
		for(int i=0;i<array.size();i++){
			jsonObject = array.getJSONObject(i);
			Integer yuefen = Integer.valueOf(jsonObject.getString("yuefen"));
			Object[] obj = {
					jsonObject.getString("tmad_name"),
					jsonObject.getString("tmad_company_name"),
					jsonObject.getString("tmad_batch_code"),
					jsonObject.getString("renshu"),
					jsonObject.getString("tmgd_code"),
					jsonObject.getString("lishu"),
					jsonObject.getString("fashenglv"),
					yuefen<3?yuefen:"",		
					yuefen>=3&&yuefen<=6?yuefen:"",
					yuefen>=6&&yuefen<=12?yuefen:"",
					yuefen>=12&&yuefen<=24?yuefen:"",
					yuefen>=24&&yuefen<=36?yuefen:"",
					yuefen>36?yuefen:"",
					jsonObject.getString("you").equals("null")?"":jsonObject.getString("you"),
					jsonObject.getString("wu").equals("null")?"":jsonObject.getString("wu"),
					jsonObject.getString("beizhu").equals("null")?"":jsonObject.getString("beizhu")
			};
			list.add(obj);
		}				
		String url = this.getRequest().getSession().getServletContext().getRealPath("/jsp/app/xls/");
		String model = url + "/model/tmaMedicineVistorReport.xls";
		String file = url + "/file/tmaMedicineVistorReport.xls";
		String rfile = "jsp/app/xls/file/tmaMedicineVistorReport.xls";
		File ffrom = new File(model);
		File fto = new File(file);
		Common.copyFile(ffrom, fto);
		List<Object[]> strList = new ArrayList<Object[]>();
		strList.add(new Object[]{"代码","副反应","代码","副反应","代码","副反应","代码","副反应","代码","副反应","代码","副反应"});
		strList.add(new Object[]{"01","痛经","02","少量出血/点滴出血","03","经期延长","04","月经周期改变","05","月经量增多","06","月经量减少"});
		strList.add(new Object[]{"代码","副反应","代码","副反应","代码","副反应","代码","副反应","代码","副反应","代码","副反应"});		
		strList.add(new Object[]{"07","恶心","08","呕吐","09","下腹痛","10","腰背酸痛","11","妊娠","12","贫血"});
		if(list.size() > 0){
			writeXls(list, fto, "药具随访汇总表", 6,strList,tianbaoren,tianbaojigou,tianbaoriqi);//写xls内容
		} else {
			return "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"导出结果集没有记录\"}";
		}
		
		return "\"" + rfile + "\"";
	}
	/** 
	 * 描述：writeXls 写XLS文件内容
	 * @param list 数据集
	 * @param file 写入文件
	 * @param title sheet页标题
	 * @param startRow xls数据起始行
	 * @throws Exception
	 * @CreateOn Sep 7, 2012  9:25:24 AM
	 * @author liu_zhaojun
	 */
	public static void writeXls(List<Object[]> list, File file, String title, int startRow,List<Object[]> strList,String tianbaoren,String tianbaojigou,String tianbaoriqi) throws Exception {
		POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(file));
		HSSFWorkbook book = new HSSFWorkbook(filein);
		HSSFSheet sheet = book.getSheet(title);
		HSSFRow row = null;
		HSSFCell cell = null;
		row = sheet.getRow(2);
		row.setHeight((short) 400);
		cell = row.getCell(2);
		cell.setCellValue(new HSSFRichTextString(tianbaojigou));
		cell = row.getCell(12);
		cell.setCellValue(new HSSFRichTextString(tianbaoriqi));
		int index = 0;
		if(null != list && list.size() > 0){
			index = list.size();
			for (int i = 0; i < list.size(); i++) {// 行
				row = sheet.createRow(i + startRow);
				row.setHeight((short) 400);
				
				//序号
				cell = row.createCell(0);
				cell.setCellValue((i+1) + "");
				
				Object[] metaData = list.get(i);
				for(int j = 0; j < metaData.length; j++) {
					if(j==metaData.length-1){
						cell = row.createCell(j+1);						
						CellRangeAddress reportNameRange = new CellRangeAddress(i + startRow ,i + startRow ,j+1,j+2);
				        sheet.addMergedRegion(reportNameRange);
					}else{
						cell = row.createCell(j + 1);
					}					
					// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
					String textValue = metaData[j].toString();
					if(!"".equals(textValue)){
						cell.setCellValue(new HSSFRichTextString(textValue));
					}					
				}
			}
		}
		index = index + startRow;		
		row = sheet.createRow(index);
		row.setHeight((short) 400);
		cell = row.createCell(15);
		cell.setCellValue(new HSSFRichTextString("填报人:"));
		cell = row.createCell(16);
		cell.setCellValue(new HSSFRichTextString(tianbaoren));
		if(null != strList && strList.size() > 0){			
			for (int i = 0; i < strList.size(); i++) {// 行
				row = sheet.createRow(i + index + 1);
				row.setHeight((short) 400);
				Object[] obj = strList.get(i);
				int c = 0;
				int d = 0;
				for(int j=0;j<obj.length;j++){
					int a = 0;
					int b = 0;					
					if(j%2!=0){							
						if(j==1){
							a = j;
							b = j+1;
						}else{
							d = d + 1;
							a = j+d;
							b = j+d+1;						
						}						
						cell = row.createCell(a);						
						CellRangeAddress reportNameRange = new CellRangeAddress(i + index + 1,i + index + 1,a,b);
				        sheet.addMergedRegion(reportNameRange);
					}else{
						if(j==0){
							a = j;
						}else{
							c = c + 1;
							a = j+c;
						}				
						cell = row.createCell(a);
					}
					// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
					String textValue = obj[j].toString();
					cell.setCellValue(new HSSFRichTextString(textValue));
				}			
			}
		}		
		FileOutputStream fileOut = new FileOutputStream(file);
		book.write(fileOut);
		fileOut.close();
	}
	
	@Override
	public Object update(Object jsonArray) throws Exception {
		return null;
	}
	
	@Override
	public Object delete(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
}


